package org.ccunix.eshop.dao;

import org.ccunix.eshop.domain.dto.CategoryVO;
import org.ccunix.eshop.domain.dto.MerchandiseVO;
import org.ccunix.eshop.utils.DataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 根据是否特价查询商品列表
 */
public class MerchandiseDao {
    public List<MerchandiseVO> selectMerchantListBySpecial(int special,String query) {
        List<MerchandiseVO> merchandiseList = new ArrayList<>();
        //1-获得数据库的链接
        Connection connection = DataSource.getConnection();
        //2-操作数据库表
        try{
            String sql="";
            if ("all".equals(query)){
                 sql = "select id,category,merName,price,sprice,merModel " +
                        ",picture,merDesc,manufacturer,leaveFactoryDate " +
                        ",special from merchandise where special=? ";
            }else{
                 sql = "select id,category,merName,price,sprice,merModel " +
                        ",picture,merDesc,manufacturer,leaveFactoryDate " +
                        ",special from merchandise where special=? " +
                        "LIMIT 3;";
            }
            //2-2获得sql语句
            //2-1获得承装sql的容器对象
            PreparedStatement ps = connection.prepareStatement(sql);
            //对？传值
            ps.setInt(1,special);
            //2-3执行sql语句(返回的是结果集)
            ResultSet set = ps.executeQuery();
            //遍历set
            while (set.next()){
                MerchandiseVO vo = new MerchandiseVO(set.getInt("id"),set.getInt("category"),
                        set.getString("merName"),set.getBigDecimal("price"), set.getBigDecimal("sprice"),
                        set.getString("merModel"), set.getString("picture"),set.getString("merDesc"),
                        set.getString("manufacturer"),set.getString("leaveFactoryDate"),set.getInt("special"));
                merchandiseList.add(vo);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //2-4关闭数据库的连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return merchandiseList;
    }

    public MerchandiseVO selectMerchantById(int id) {
        MerchandiseVO vo = null;
        //1-获得数据库的链接
        Connection connection = DataSource.getConnection();
        //2-操作数据库表
        try{
            //2-2获得sql语句
            String sql = "select id,category,merName,price,sprice,merModel " +
                    ",picture,merDesc,manufacturer,DATE_FORMAT(leaveFactoryDate,'%Y-%m-%d') leaveFactoryDate" +
                    ",special from merchandise where id=? ";
            //2-1获得承装sql的容器对象
            PreparedStatement ps = connection.prepareStatement(sql);
            //对？传值
            ps.setInt(1,id);
            //2-3执行sql语句(返回的是结果集)
            ResultSet set = ps.executeQuery();
            //遍历set
            if (set.next()){
                 vo = new MerchandiseVO(set.getInt("id"),set.getInt("category"),
                        set.getString("merName"),set.getBigDecimal("price"), set.getBigDecimal("sprice"),
                        set.getString("merModel"), set.getString("picture"),set.getString("merDesc"),
                        set.getString("manufacturer"),set.getString("leaveFactoryDate"),set.getInt("special"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //2-4关闭数据库的连接
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return vo;
    }
}
